package ims.db.search.custOrder;

import ims.beans.CustomerOrderSummary;
import ims.db.AbstractDAO;
import ims.db.AbstractDbRequest;
import ims.db.AbstractDbResponse;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class SearchCustOrderDAO extends AbstractDAO {

	public SearchCustOrderDAO() throws ClassNotFoundException, SQLException {
		super();
	}

	@Override
	public AbstractDbResponse execute(AbstractDbRequest dbRequest) 
	{
		SearchCustOrderDbResponse response = new SearchCustOrderDbResponse();
		SearchCustOrderDbRequest request = (SearchCustOrderDbRequest)dbRequest;
        try 
        {
        	String select="Select distinct(co.invoiceNum), u.firstname,u.lastname, to_char(co.orderdate,'MM-DD-YYYY'), co.ordertype ";
        	String from = "from customerorder co, useraccount u ";
        	StringBuffer where = new StringBuffer();
        	where.append(" where co.userid=u.userid ");
        	if (!request.getOrderNum().equals(""))
        		where.append(" and co.invoicenum='").append(request.getOrderNum()).append("' ");
        	
        	if(!request.getOrderType().equals("All"))
        		where.append(" and co.ordertype='").append(request.getOrderType()).append("' ");
        	
        	if(!request.getOrderDt().equals(""))
        		where.append(" and co.orderdate=to_date('").append(request.getOrderDt()).append("','MM-DD-YYYY') ");
        	
        	if(!request.getCustFname().equals(""))
        		where.append(" and u.firstname='").append(request.getCustFname()).append("' ");
        	
        	if(!request.getCustLname().equals(""))
        		where.append(" and u.lastname='").append(request.getCustLname()).append("' ");
        	
        	String sql = select + from + where.toString();
        	
        	
        	Statement stmt = conn.createStatement();
        	ResultSet rs = stmt.executeQuery(sql);
        	ArrayList <CustomerOrderSummary> summary = new ArrayList <CustomerOrderSummary>();
        	while(rs.next())
        	{
        		CustomerOrderSummary order = new CustomerOrderSummary();
        		order.setOrderNum(rs.getString(1));
        		order.setCustName(rs.getString(2)+" "+rs.getString(3));
        		order.setOrderDt(rs.getString(4));
        		order.setOrderType(rs.getString(5));
        		summary.add(order);
        	}
        	response.setOrders(summary);
            stmt.close();
            conn.close();
        } 
        catch (SQLException sqlEx)
        {
        	sqlEx.printStackTrace();
        }
        return response;
	}
}
